import psycopg2
import json
from datetime import datetime

# 数据库连接配置
DB_CONFIG = {
    "dbname": "game_trading_v2",
    "user": "postgres",
    "password": "145114",
    "host": "localhost",
    "port": "5432"
}

def get_conn():
    """获取数据库连接"""
    return psycopg2.connect(**DB_CONFIG)

# ============ 核心装备管理函数 ============

def add_equipment(user_id, name, category, level, rarity, price, attributes=None, image_url=None):
    """
    新增装备
    
    参数:
        user_id: 发布者ID
        name: 装备名称
        category: 装备类别
        level: 装备等级
        rarity: 稀有度
        price: 价格
        attributes: 装备属性（JSON格式）
        image_url: 装备图片URL
        
    返回:
        成功返回(True, 装备ID)，失败返回(False, 错误信息)
    """
    try:
        # 数据验证
        if not name or not price:
            return False, "装备名称和价格不能为空"
        
        try:
            price = float(price)
            if price <= 0:
                return False, "价格必须大于零"
        except ValueError:
            return False, "价格格式不正确"
            
        # 格式化属性为JSON
        if attributes and isinstance(attributes, dict):
            attributes_json = json.dumps(attributes)
        elif attributes and isinstance(attributes, str):
            try:
                # 尝试解析字符串为JSON
                json.loads(attributes)
                attributes_json = attributes
            except:
                return False, "属性格式不正确"
        else:
            attributes_json = None
        
        with get_conn() as conn:
            with conn.cursor() as cur:
                # 插入新装备
                sql = """
                    INSERT INTO Equipments 
                    (SellerID, EquipmentName, Category, Level, Rarity, Attributes, Price, ListedDate, Status, ImageURL) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    RETURNING EquipmentID
                """
                cur.execute(sql, (
                    user_id, name, category, level, rarity, attributes_json, 
                    price, datetime.now(), "active", image_url
                ))
                equipment_id = cur.fetchone()[0]
                conn.commit()
                return True, equipment_id
    except Exception as e:
        print(f"添加装备错误: {e}")
        return False, str(e)

def get_equipment_details(equipment_id):
    """
    获取装备详细信息
    
    参数:
        equipment_id: 装备ID
        
    返回:
        装备详细信息字典，失败返回None
    """
    try:
        with get_conn() as conn:
            with conn.cursor() as cur:
                sql = """
                    SELECT e.EquipmentID, e.EquipmentName, e.Category, e.Level, e.Rarity, 
                           e.Attributes, e.Price, e.ListedDate, e.Status, e.ImageURL, e.SellerID,
                           u.Username as SellerName
                    FROM Equipments e
                    JOIN Users u ON e.SellerID = u.UserID
                    WHERE e.EquipmentID = %s
                """
                cur.execute(sql, (equipment_id,))
                row = cur.fetchone()
                
                if not row:
                    return None
                    
                # 将结果格式化为字典
                result = {
                    'EquipmentID': row[0],
                    'EquipmentName': row[1],
                    'Category': row[2],
                    'Level': row[3],
                    'Rarity': row[4],
                    'Attributes': row[5] if row[5] else {},  # JSON属性
                    'Price': float(row[6]),
                    'ListedDate': row[7],
                    'Status': row[8],
                    'ImageURL': row[9],
                    'SellerID': row[10],
                    'SellerName': row[11]
                }
                return result
    except Exception as e:
        print(f"获取装备详情错误: {e}")
        return None

def update_equipment(equipment_id, user_id, **kwargs):
    """
    更新装备信息
    
    参数:
        equipment_id: 装备ID
        user_id: 操作用户ID（必须是装备拥有者）
        **kwargs: 要更新的字段，可包含 name, category, level, rarity, price, attributes, image_url
        
    返回:
        成功返回(True, 消息)，失败返回(False, 错误信息)
    """
    try:
        # 检查装备是否属于该用户
        if not check_equipment_owner(equipment_id, user_id):
            return False, "您没有权限修改此装备"
            
        # 检查装备状态
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT Status FROM Equipments WHERE EquipmentID = %s", (equipment_id,))
                status = cur.fetchone()[0]
                if status != "active":
                    return False, f"装备当前状态为{status}，无法修改"
        
        # 构建更新SQL
        fields = []
        params = []
        old_price = None
        new_price = None
        
        if 'name' in kwargs and kwargs['name']:
            fields.append("EquipmentName = %s")
            params.append(kwargs['name'])
            
        if 'category' in kwargs:
            fields.append("Category = %s")
            params.append(kwargs['category'])
            
        if 'level' in kwargs:
            fields.append("Level = %s")
            params.append(kwargs['level'])
            
        if 'rarity' in kwargs:
            fields.append("Rarity = %s")
            params.append(kwargs['rarity'])
        
        # 处理价格变更，需要记录旧价格以便发送价格变动消息    
        if 'price' in kwargs:
            try:
                price = float(kwargs['price'])
                if price <= 0:
                    return False, "价格必须大于零"
                    
                # 获取旧价格
                with get_conn() as conn:
                    with conn.cursor() as cur:
                        cur.execute("SELECT Price FROM Equipments WHERE EquipmentID = %s", (equipment_id,))
                        old_price = float(cur.fetchone()[0])
                        new_price = price
                
                fields.append("Price = %s")
                params.append(price)
            except ValueError:
                return False, "价格格式不正确"
                
        if 'attributes' in kwargs and kwargs['attributes']:
            if isinstance(kwargs['attributes'], dict):
                fields.append("Attributes = %s")
                params.append(json.dumps(kwargs['attributes']))
            elif isinstance(kwargs['attributes'], str):
                try:
                    json.loads(kwargs['attributes'])  # 验证JSON格式
                    fields.append("Attributes = %s")
                    params.append(kwargs['attributes'])
                except:
                    return False, "属性JSON格式不正确"
                    
        if 'image_url' in kwargs:
            fields.append("ImageURL = %s")
            params.append(kwargs['image_url'])
            
        if not fields:
            return False, "没有提供任何要更新的字段"
            
        # 执行更新操作
        with get_conn() as conn:
            with conn.cursor() as cur:
                sql = f"UPDATE Equipments SET {', '.join(fields)} WHERE EquipmentID = %s"
                params.append(equipment_id)
                cur.execute(sql, tuple(params))
                
                # 如果有价格变更，发送价格变动消息
                if old_price is not None and new_price is not None and old_price != new_price:
                    # 查找所有将此装备加入购物车的用户
                    cur.execute("""
                        SELECT DISTINCT UserID 
                        FROM Cart 
                        WHERE EquipmentID = %s AND UserID != %s
                    """, (equipment_id, user_id))
                    user_ids = [row[0] for row in cur.fetchall()]
                    
                    # 为每个用户创建价格变动消息
                    for uid in user_ids:
                        create_price_change_message(cur, uid, equipment_id, old_price, new_price)
                        
                conn.commit()
                return True, "装备信息已更新"
    except Exception as e:
        print(f"更新装备错误: {e}")
        return False, str(e)

def remove_equipment(equipment_id, user_id):
    """
    下架装备
    
    参数:
        equipment_id: 装备ID
        user_id: 操作用户ID（必须是装备拥有者）
        
    返回:
        成功返回(True, 消息)，失败返回(False, 错误信息)
    """
    try:
        # 检查装备是否属于该用户
        if not check_equipment_owner(equipment_id, user_id):
            return False, "您没有权限下架此装备"
            
        with get_conn() as conn:
            with conn.cursor() as cur:
                # 检查装备状态
                cur.execute("SELECT Status FROM Equipments WHERE EquipmentID = %s", (equipment_id,))
                status = cur.fetchone()[0]
                if status != "active":
                    return False, f"装备当前状态为{status}，无法下架"
                    
                # 更新装备状态为已下架
                cur.execute(
                    "UPDATE Equipments SET Status = 'inactive' WHERE EquipmentID = %s", 
                    (equipment_id,)
                )
                
                # 从所有购物车中移除该装备
                cur.execute("DELETE FROM Cart WHERE EquipmentID = %s", (equipment_id,))
                removed_count = cur.rowcount
                
                conn.commit()
                return True, f"装备已下架，同时从{removed_count}个购物车中移除"
    except Exception as e:
        print(f"下架装备错误: {e}")
        return False, str(e)

# ============ 辅助工具函数 ============

def check_equipment_owner(equipment_id, user_id):
    """
    检查用户是否是装备拥有者
    
    参数:
        equipment_id: 装备ID
        user_id: 用户ID
        
    返回:
        布尔值，是否是拥有者
    """
    try:
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT SellerID FROM Equipments WHERE EquipmentID = %s", (equipment_id,))
                row = cur.fetchone()
                if not row:
                    return False
                return str(row[0]) == str(user_id)
    except Exception as e:
        print(f"检查装备拥有者错误: {e}")
        return False

def create_price_change_message(cursor, user_id, equipment_id, old_price, new_price):
    """
    创建价格变动消息
    
    参数:
        cursor: 数据库游标
        user_id: 接收消息的用户ID
        equipment_id: 装备ID
        old_price: 旧价格
        new_price: 新价格
    """
    try:
        cursor.execute("""
            INSERT INTO PriceChangeMessages 
            (UserID, EquipmentID, OldPrice, NewPrice, ChangeDate, IsRead)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (user_id, equipment_id, old_price, new_price, datetime.now(), False))
    except Exception as e:
        print(f"创建价格变动消息错误: {e}")

def validate_equipment_data(name, category, level, rarity, price):
    """
    验证装备数据有效性
    
    参数:
        name: 装备名称
        category: 装备类别
        level: 装备等级
        rarity: 稀有度
        price: 价格
        
    返回:
        成功返回(True, None)，失败返回(False, 错误信息)
    """
    if not name or len(name.strip()) == 0:
        return False, "装备名称不能为空"
        
    if not category:
        return False, "请选择装备类别"
        
    try:
        level_int = int(level)
        if level_int <= 0:
            return False, "装备等级必须大于0"
    except:
        return False, "装备等级必须是正整数"
        
    if not rarity:
        return False, "请选择装备稀有度"
        
    try:
        price_float = float(price)
        if price_float <= 0:
            return False, "价格必须大于0"
    except:
        return False, "价格格式不正确"
        
    return True, None

def format_equipment_details(equipment_data):
    """
    格式化装备详情用于显示
    
    参数:
        equipment_data: 装备数据字典
        
    返回:
        格式化后的详情字符串
    """
    if not equipment_data:
        return "装备不存在或已被删除"
        
    details = []
    details.append(f"装备名称: {equipment_data['EquipmentName']}")
    details.append(f"装备ID: {equipment_data['EquipmentID']}")
    
    if equipment_data.get('Category'):
        details.append(f"类别: {equipment_data['Category']}")
        
    if equipment_data.get('Level'):
        details.append(f"等级: {equipment_data['Level']}")
        
    if equipment_data.get('Rarity'):
        details.append(f"稀有度: {equipment_data['Rarity']}")
        
    details.append(f"价格: ¥{equipment_data['Price']}")
    details.append(f"状态: {equipment_data['Status']}")
    details.append(f"上架时间: {equipment_data['ListedDate']}")
    details.append(f"卖家: {equipment_data['SellerName']} (ID: {equipment_data['SellerID']})")
    
    # 添加属性信息
    if equipment_data.get('Attributes') and equipment_data['Attributes']:
        details.append("\n属性:")
        attributes = equipment_data['Attributes']
        if isinstance(attributes, str):
            try:
                attributes = json.loads(attributes)
            except:
                attributes = {}
                
        for key, value in attributes.items():
            details.append(f"  {key}: {value}")
    
    return '\n'.join(details)

def reactivate_equipment(equipment_id, user_id):
    """重新上架装备
    
    Args:
        equipment_id: 装备ID
        user_id: 用户ID
        
    Returns:
        tuple: (success, message)
    """
    try:
        # 检查装备是否存在且归属于该用户
        with get_conn() as conn:
            with conn.cursor() as cur:
                # 先查询装备状态
                cur.execute(
                    "SELECT Status FROM Equipments WHERE EquipmentID = %s AND SellerID = %s",
                    (equipment_id, user_id)
                )
                result = cur.fetchone()
                
                if not result:
                    return False, "找不到该装备或您没有权限操作此装备"
                
                current_status = result[0]
                
                # 检查状态是否为inactive
                if current_status.strip() == "inactive":
                    # 更新装备状态为出售中/active
                    cur.execute(
                        "UPDATE Equipments SET Status = 'active' WHERE EquipmentID = %s",
                        (equipment_id,)
                    )
                    conn.commit()
                    return True, "装备已成功重新上架"
                else:
                    return False, f"只有已下架(inactive)的装备才能重新上架，当前状态: {current_status}"
    except Exception as e:
        print(f"重新上架装备错误: {e}")
        return False, f"操作失败: {str(e)}"